package EBM.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import EBM.model.Project;
import EBM.model.User;

public class ProjectUserDAO {
	private List<Object> response;
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private String sql;
	private int result;

	public ProjectUserDAO(){
		response = null;
		conn = null;
		ps = null;
		rs = null;
		sql = null;
		result = 0;
	}
	
	public boolean save(Object proid, Object userid) 
	throws SQLException, ClassNotFoundException{

		if (proid == null || userid == null)
			return false;
		
		String sql="insert into project_user(proid,usrid) values(?,?)";
        
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();

		ps.setObject(1, proid);
		ps.setObject(2, userid);

		try
		{
			result = ps.executeUpdate();
		}
		catch (Exception ex)
		{
			result = -1;
		}
		finally
		{
			conn.close();			
		}
		
		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	private boolean canbedelete(String proid, String userid) throws SQLException, ClassNotFoundException
	{
		ProjectDAO pDAO = new ProjectDAO();
		List<Object> projects = pDAO.findById(proid);
		if (projects == null || projects.size() == 0)
			return false;
		String sql = "SELECT * FROM bug_user_" + ((Project)(projects.get(0))).getProid() + " where userid = ?";
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, userid);
		ResultSet rs = ps.executeQuery();
		if (rs.next())
			return false;
		else return true;
	}
	
	public boolean delete(String proid, String userid) throws SQLException, ClassNotFoundException
	{
		if (canbedelete(proid, userid) == false)
			return false;
		
		String sql="delete from project_user where proid=? and usrid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();

		ps.setObject(1, proid);
		ps.setObject(2, userid);

		result = ps.executeUpdate();
		conn.close();
		
		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	public List<Object> findByProid(Object proId) 
	throws SQLException, ClassNotFoundException{

		sql="select * from project_user,userinfo where project_user.usrid=userinfo.id and proid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		ps.setObject(1, proId);

		rs=ps.executeQuery();

		response = new ArrayList<Object>();
		if(rs.next()){
			do{
				User u = new User();
				u.setId(rs.getObject("userinfo.id").toString());
				u.setUsrid(rs.getObject("userinfo.usrid").toString());
				u.setName(rs.getObject("userinfo.name").toString());
				u.setType_usr(rs.getObject("userinfo.type_usr").toString());
				response.add(u);
			}while(rs.next());
		}
		conn.close();

		return response;
	}
	
	public List<Object> findExceptProid(Object proId) 
	throws SQLException, ClassNotFoundException{

		//type_usr<2指不能使审查用户
		sql="select * from userinfo where type_usr < 2 and id not in (select userinfo.id from userinfo,project_user where userinfo.id=project_user.usrid and proid=?)";

		//System.out.println(sql);
		
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		ps.setObject(1, proId);

		rs=ps.executeQuery();

		response = new ArrayList<Object>();
		if(rs.next()){
			do{
				User u = new User();
				u.setId(rs.getObject("userinfo.id").toString());
				u.setUsrid(rs.getObject("userinfo.usrid").toString());
				u.setName(rs.getObject("userinfo.name").toString());
				u.setType_usr(rs.getObject("userinfo.type_usr").toString());
				response.add(u);
			}while(rs.next());
		}
		conn.close();

		return response;
	}
}
